using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

/// <summary>
/// Summary description for DAl
/// </summary>
public class DAL
{
    private string strConnectionString =System.Web.Configuration.WebConfigurationManager.AppSettings["connectionString"];
    private MySqlConnection conn;
    private MySqlCommand comm;
    private MySqlDataAdapter da;
    private DataSet ds;
	public DAL()
	{
        conn = new MySqlConnection(strConnectionString);
        ds = new DataSet();
	}

    public DataTable getPages()
    {
        comm = new MySqlCommand("SELECT * FROM Pages",conn);
        da = new MySqlDataAdapter(comm);
        
        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }
        if (ds.Tables[0] != null)
            return ds.Tables[0];
        return null;
    }

    public DataTable getPage(string page_Id)
    {
        string strCommand = "SELECT * FROM Pages WHERE Pages_Id=" + page_Id;
        comm = new MySqlCommand(strCommand, conn);
        da = new MySqlDataAdapter(comm);
        ds.Clear();

        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }

        return ds.Tables[0];
    }

    public string getPage_Menu_Title(string page_Id)
    {
        string strCommand = "SELECT Menu_title FROM Pages WHERE Pages_Id=" + page_Id;
        comm = new MySqlCommand(strCommand, conn);
        da = new MySqlDataAdapter(comm);
        ds.Clear();

        try
        {
            da.Fill(ds);
        }
        catch
        {
            return "";
        }

        if (ds.Tables[0] != null)
            return ds.Tables[0].Rows[0]["Menu_title"].ToString();
        return "";
    }

    public DataTable getArticles(string pages_Id)
    {
        string strCommand = "SELECT * FROM Articles WHERE Pages_Id=" + pages_Id + " ORDER BY Date_Added DESC;";
        comm = new MySqlCommand(strCommand,conn);
        da = new MySqlDataAdapter(comm);

        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }
        return ds.Tables[0];
    }

    public DataTable getArticle(string articles_id)
    {
        string strCommand = "SELECT * FROM Articles WHERE Articles_id=" + articles_id;
        comm = new MySqlCommand(strCommand, conn);
        da = new MySqlDataAdapter(comm);
        ds.Clear();
        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }
        return ds.Tables[0];
    }

    public DataSet searchData(string searchCriteria)
    {
        comm = new MySqlCommand("SELECT * FROM Pages WHERE Content like '%"+searchCriteria
            +"%'; SELECT * FROM Articles WHERE Content like '%"+searchCriteria+"%';", conn);
        da = new MySqlDataAdapter(comm);
        try
        {
            da.Fill(ds);
        }
        catch
        {
            ds = null;
        }

        return ds;
    }

    public bool insertPage(string has_Article, string menu_Title, string title, string content,string date_Added)
    {
        bool bResult = false;
        string strCommand = "INSERT INTO Pages (Has_Article,Menu_title,Title,Content,Date_Added) VALUES ('" + has_Article + "','" + menu_Title + "','" + title + "','" + content + "','" + date_Added + "');";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool updatePage(string page_ID,string has_Article, string menu_Title, string title, string content, string date)
    {
        bool bResult = false;
        string strCommand = "UPDATE Pages SET Has_Article='"+has_Article+"',Menu_title='" + menu_Title + "',Title='" + title + "',Content='" + content + "',Date_Added='"+date+"' WHERE Pages_Id="+page_ID+";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool deletePage(string Page_Id)
    {
        bool bResult = false;
        string strCommand = "DELETE FROM `Pages` WHERE `Pages_Id`=" + Page_Id + ";";
        strCommand += "DELETE FROM `Articles` WHERE `Pages_Id`=" + Page_Id + ";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool insertArticle(string pages_Id, string title, string content, string date_added)
    {
        bool bResult = false;
        string strCommand = "INSERT INTO `articles` ( `Articles_Id` , `Pages_Id` , `Title` , `Content` , `Date_added` ) VALUES ('', '" + pages_Id + "', '" + title + "', '" + content + "', '" + date_added + "');";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool updateArticle(string article_Id,string page_Id, string title, string content, string date)
    {
        bool bResult = false;
        string strCommand = "UPDATE Articles SET pages_Id='" + page_Id + "',Title='" + title + "',Content='" + content + "',Date_Added='" + date + "' WHERE Articles_Id=" + article_Id + ";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool deleteArticle(string article_Id)
    {
        bool bResult = false;
        string strCommand = "DELETE FROM `Articles` WHERE `articles_Id`=" + article_Id + ";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public DataTable getLinks()
    {
        comm = new MySqlCommand("SELECT * FROM bookmarks", conn);
        da = new MySqlDataAdapter(comm);

        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }
        if (ds.Tables[0] != null)
            return ds.Tables[0];
        return null;
    }

    public DataTable getLink(string bookmarks_Id)
    {
        string strCommand = "SELECT * FROM bookmarks WHERE bookmarks_id=" + bookmarks_Id;
        comm = new MySqlCommand(strCommand, conn);
        da = new MySqlDataAdapter(comm);
        ds.Clear();
        try
        {
            da.Fill(ds);
        }
        catch
        {
            return null;
        }
        return ds.Tables[0];
    }

    public bool insertLink(string Title, string URL, string Description, string Target)
    {
        bool bResult = false;
        string strCommand = "INSERT INTO `bookmarks` ( `bookmarks_Id` , `URL` , `Title` , `Description` , `Target` ) VALUES ('', '" + URL + "', '" + Title + "', '" + Description + "', '" + Target + "');";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool updateLink(string bookmarks_Id,string Title, string URL, string Description, string Target)
    {
        bool bResult = false;
        string strCommand = "UPDATE bookmarks SET URL='" + URL + "',Title='" + Title + "',Description='" + Description + "',Target='" + Target + "' WHERE bookmarks_Id=" + bookmarks_Id + ";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }

    public bool deleteLink(string bookmarks_Id)
    {
        bool bResult = false;
        string strCommand = "DELETE FROM `bookmarks` WHERE `bookmarks_Id`=" + bookmarks_Id + ";";
        comm = new MySqlCommand(strCommand, conn);
        try
        {
            conn.Open();
            comm.ExecuteNonQuery();
            bResult = true;
        }
        catch
        {
        }
        finally
        {
            conn.Close();
        }
        return bResult;
    }
}
